package devicedbgui.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

/** Interface to database for acquiring Build information */
public class BuildDAO {
    /** Stores Connection status */
    private Connection conn;
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    
    /** Constructor used to pass connection status to object
     * @param conn  connection status
     */
    public BuildDAO(Connection conn)
    {
        this.conn = conn;
    }
    /** Selects all builds in database
     * 
     * @throws java.lang.Exception
     */
    public ArrayList<Build> selectAll() throws Exception {
        ArrayList<Build> result = new ArrayList<Build>();

        try {
            String query = "SELECT * FROM Build;";           
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Build pObj = new Build(
                        rs.getInt("idBuild"),
                        rs.getTimestamp("start"),
                        rs.getTimestamp("end"),
                        rs.getString("Device_record")
                        );
                result.add(pObj);
            }

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return result;
    }
    /** Inserts an new build record into database
     * 
     * @param pObj
     * @throws java.lang.Exception
     */
    public void insert(Build pObj) throws Exception {
       
        try {
            String insert = "INSERT INTO Build(" +                    
                    "start," +
                    "end," +
                    "Device_record)" +
                    "VALUES('" + sdf.format(pObj.start) + "','" +
                                 sdf.format(pObj.end) + "','" +                                 
                                 pObj.Device_record + "');";

            
            PreparedStatement pstmt = conn.prepareStatement(insert);
            pstmt.executeUpdate();            

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
    }
/** Updates database with the modified build information 
 * 
 * @param pObj
 * @throws java.lang.Exception
 */
    public void update(Build pObj) throws Exception {        
        try {

            String update = "UPDATE Build SET " +
                    "start = '" + sdf.format(pObj.start) + "'," +
                    "end = '" + sdf.format(pObj.end) +  "'," +                    
                    "Device_record = '" + pObj.Device_record +
                    "' WHERE idBuild = '" + pObj.idBuild + "';";
            
           
            PreparedStatement pstmt = conn.prepareStatement(update);
            pstmt.executeUpdate();
            pstmt.close();
           

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
    }
/** Deletes a value from database
 * 
 * @param pId
 * @throws java.lang.Exception
 */
    public void delete(int pId) throws Exception {
       
        try {
            String delete = "DELETE FROM Build WHERE idBuild = '" + pId + "';";
          
            PreparedStatement pstmt = conn.prepareStatement(delete);
            pstmt.executeUpdate();
           

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
    }
    
    /** Chooses build based on device record
     * 
     * @param Device_record
     * 
     * @return result
     * @throws java.lang.Exception
     */
    public ArrayList<Build> selectByDevice(String Device_record) throws Exception {
        ArrayList<Build> result = new ArrayList<Build>();
        try {
            String query = "SELECT * FROM Build where Device_record = '" + Device_record +"';";           
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Build pObj = new Build(
                        rs.getInt("idBuild"),
                        rs.getTimestamp("start"),
                        rs.getTimestamp("end"),
                        rs.getString("Device_record")
                        );
                result.add(pObj);
            }

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return result;
    }
}
